package ibm.etp.bean;

import ibm.etp.util.HibernateUtil;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadEls {

	private static boolean logEnable = false;
	
	public static void setLogEnable(boolean enable){
		logEnable = enable;
	}
	
	
	public static List<String[][]> read(File file) throws BiffException,
			IOException, Exception {
		
		List<String[][]> contents = new ArrayList<String[][]>();
		Workbook book = Workbook.getWorkbook(file);
		Sheet[] sheets = book.getSheets();
		String[][] row_contents = null;
		for (int sheet_index = 0; sheet_index < sheets.length; sheet_index++) {
		
		
			Sheet sheet = sheets[sheet_index];
			
			int rows = sheet.getRows();

			int columns = sheet.getColumns();			
			
			
			row_contents = new String[rows][columns];
		
			for (int row_index = 0; row_index < rows; row_index++) {
			
				String[] column_contents = new String[columns];
				for (int column_index = 0; column_index < columns; column_index++) {
					
					Cell cell = sheet.getCell(column_index, row_index);
					
					column_contents[column_index] = cell.getContents();
				}
				
				row_contents[row_index] = column_contents;
			}
			
			
			for (int j = 1; j < row_contents.length; j++) {
				//for (int k = 0; k < row_contents[j].length; k++) {
					
					
					String wid = row_contents[j][0];
					String username = row_contents[j][1];
					String password = row_contents[j][2];
					String sex = row_contents[j][3];
					String age = row_contents[j][4];
					String wdate = row_contents[j][5];
					String address = row_contents[j][6];
					String salary = row_contents[j][7];
					
					SimpleDateFormat Hidf = new SimpleDateFormat("yyyy-MM-dd");
					Worker worke = new Worker();
					worke.setWid(wid);
					worke.setUsername(username);
					worke.setPassword(password);
					worke.setAge(Integer.decode(age));
					worke.setSex(sex);
					worke.setWdate(Hidf.parse(wdate));
					worke.setAddress(address);
					worke.setSalary(Double.valueOf(salary));

					Session session = HibernateUtil.currentSession();
					session.beginTransaction();// connection.setAutoCommit(false);
					session.save(worke);
					session.getTransaction().commit();
					
					
					//System.out.println("["+j+"]["+k+"]"+row_contents[j][k] + "\t");
					
				//}
				//System.out.println("\n");
			}
			

	
			contents.add(row_contents);
		}

		return contents;
	}
	
	private static void log(String str){
		if(logEnable){
			System.out.print(str);
		}
	}

/*	public static void main(String[] args) throws Exception {
		try {
			read(new File("./resources/Book1.xls"));
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}*/
}
